import pymysql
import pandas as pd
import datetime

import pymysql.cursors

class MysqlUtils(object):
    def __inin__(self):
        self.conn = pymysql.connect(
            host="127.0.0.1",
            user="root",
            passwd ="root",
            db="tushare",
            port=3306,
            charset="utf8"
        )

class classIfication(object):
    def __init__(self):
        pass

    def get_fina_indicator(self,conn):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql = "SELECT rs_code,ann_date,eps,total_revenue_ps,undist_profit_ps,gross_margin,fcff,fcfe,tangible_asset,bps,grossprofit_margin,npta FROM financial_d"

        cursor.execute(sql)
        ret = cursor.fetchall()

        df = pd.DataFrame(ret)
        df1 = df.dropna(subset=['eps','total_revenue_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])
        df1 = df1.reset_index(drop=False)

        return df1
    
    def get_daily(self,conn,df):
        cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
        new_list = []

        for i in range(len(df['ts_code'])):
            ann_date = df['ann_date'][i].strftime('%Y%m%d')
            sql = "select trade_date,closes from date_1 where ts_code = \'"+ df['ts_code'][i] + "\' and trade_date > Date(\'"+ann_date+"\') order by trade_date asc limit 20"

            cursor.execute(sql)
            ret = cursor.fetchall()
            df1 = pd.DataFrame(ret)
            print(i)
            try:
                if len(df1) > 0 :
                    max_close = df1['closes'].max()
                    min_close = df1["closes"].min()
                    the_close = df1['closes'].iloc[1]

                    new_list.append({
                        'ts_code': df['ts_code'][i],
                        'ann_date': df['ann_date'][i],
                        'max_close': max_close,
                        'min_close': min_close,
                        'the_close': the_close,
                        'eps':df['eps'][i],
                        'total_revenue_ps': df['total_revenue_ps'][1],
                        'undist_profit_ps': df['undist_profit_ps'][1],
                        'gross_margin': df['gross_margin'][i],
                        'fcff': df['fcff'][i],
                        'fcfe': df['fcfe'][i],
                        'tangible_asset': df['tangible_asset'][1],
                        "bps":df['bps'][i],
                        'grossprofit_margin': df['grossprofit_margin'][i],
                        'npta': df['npta'][i],
                    })
            except Exception as e:
                print(e)
        df2 = pd.DataFrame(new_list)
        df2.to_csv('daily.csv',index=False)

if __name__=="__main__":
    mu =MysqlUtils()
    ci = classIfication()
    ci.get_fina_indicator(mu.conn)